-- =============================================
-- Author:		<Kaung Myat Bo>
-- Create date: <12/10/2014>
-- Description:	<Remittance Serials>
-- =============================================
CREATE PROCEDURE sp_rmt_serial
@param1 bigint, --BranchId
@param2 date, -- TrDate
@param3 varchar(20) -- Code
AS
BEGIN
declare @serial int;
if not exists (select Serial from RemittanceSerial where BranchId = @param1 and TrDate = @param2 and Code = @param3)
	begin
		set @serial = 1;
		insert into RemittanceSerial(BranchId, Code, TrDate, Serial) values(@param1, @param3, @param2, @serial);
	end
else
	begin
		select @serial = Serial + 1 from RemittanceSerial where BranchId = @param1 and TrDate = @param2 and Code = @param3;
		update RemittanceSerial set Serial = @serial where BranchId = @param1 and TrDate = @param2 and Code = @param3;
	end
declare @branchCode varchar(20);
set @branchCode = (select BranchCode from Branch where BranchId = @param1);
select @branchCode + @param3 + convert(varchar,year(@param2)%100) + convert(varchar,month(@param2)) + convert(varchar,day(@param2)) + '/' + dbo.[FN_LPAD_LEADING_ZERO] (5,@serial)
END
GO

